Come restituire un valore se un determinato valore esiste in un certo intervallo in Excel?
Quando si lavora con i dati in Excel, è spesso necessario determinare se un valore specifico esiste all'interno di un intervallo definito e, in caso affermativo, recuperare un valore dalla cella adiacente corrispondente a quella voce. Ad esempio, come mostrato nello screenshot a sinistra, se stai cercando il numero 5 all'interno di un elenco o intervallo, potresti voler restituire automaticamente il valore adiacente corrispondente – utile per attività come la ricerca di ID prodotto, il recupero di informazioni utente o l'abbinamento di codici a valori senza ricerca manuale.
Restituisci un valore se un dato valore esiste in un certo intervallo
Restituisci un valore se un dato valore esiste in un certo intervallo utilizzando la funzione CERCA.VERT
Per recuperare rapidamente un valore associato a una voce specifica da una tabella dati o intervallo in Excel, la funzione CERCA.VERT offre una soluzione semplice.
Questo metodo è particolarmente efficace se la colonna di ricerca (dove cerchi il valore) è la colonna più a sinistra del tuo intervallo dati e vuoi restituire dati da una colonna alla sua destra. Viene comunemente usato per cercare codici, nomi, ID o numeri di riferimento e recuperare facilmente dettagli correlati.
1. Seleziona una cella vuota dove desideri che appaia il risultato. Inserisci la seguente formula nella Barra delle formule:
=VLOOKUP(E2,A2:C8,3,TRUE)Premi Invio per eseguire la formula. Vedi screenshot:
In questo esempio, se il numero 5 (nella cella E2) viene trovato all'interno dell'intervallo numerico specificato nella colonna A (ad esempio, tra 4 e 6), Excel cercherà questo valore e popolerà immediatamente il valore corrispondente dalla terza colonna (colonna C) dell'intervallo A2:C8 nella tua cella selezionata. Nell'illustrazione, “Addin 012” viene restituito perché il numero 5 si trova nell'intervallo 4-6.
Nota: Nella formula, E2 si riferisce al valore di ricerca, A2:C8 è l'intervallo dati che comprende i valori di ricerca e i risultati, e 3 specifica che il valore restituito dovrebbe provenire dalla terza colonna dell'intervallo specificato. Modifica questi riferimenti in base alle esigenze del tuo foglio di lavoro.
Consigli e insidie:
- Assicurati che l'intervallo di ricerca (A2:C8) includa sia la colonna di ricerca che la colonna di ritorno.
- Quando si utilizza CERCA.VERT con l'argomento VERO, la colonna di ricerca deve essere ordinata in ordine crescente, altrimenti potresti ottenere risultati imprevisti.
- Per corrispondenze esatte, usa FALSO come quarto argomento, ma per ricerche di intervallo (come in questo esempio), lascialo come VERO.
- Se i tuoi dati cambiano frequentemente, verifica attentamente i tuoi riferimenti per evitare errori di disallineamento.
Restituisci un valore se un dato valore esiste in un certo intervallo utilizzando le funzioni INDICE e CONFRONTA
La combinazione INDICE e CONFRONTA è un metodo flessibile per restituire un valore quando un dato valore esiste in un certo intervallo. A differenza di CERCA.VERT, INDICE e CONFRONTA possono cercare un valore in qualsiasi colonna e restituire un risultato da qualsiasi altra colonna, indipendentemente dall'ordine. Questo è particolarmente utile se la colonna di ricerca non è la colonna più a sinistra o se hai bisogno di maggiore adattabilità nella tua struttura dati.
1. Seleziona una cella vuota dove desideri che appaia il risultato (ad esempio, F2). Inserisci la seguente formula nella Barra delle formule:
=INDEX(C2:C8, MATCH(E2, A2:A8,1))Premi Invio per confermare la formula.
- CONFRONTA(E2, A2:A8, 1) cerca la posizione del valore più grande ≤ E2 nella colonna A. (Ciò richiede che la colonna A sia ordinata in ordine crescente.)
- INDICE(C2:C8, ...) restituisce il valore dalla colonna C alla riga trovata da CONFRONTA.
Questa formula cerca il valore in E2 all'interno dell'intervallo A2:A8. Se il valore viene trovato (ad esempio, 5 è compreso tra 4 e 6 in una delle righe), la funzione CONFRONTA restituisce la sua posizione relativa e INDICE recupera il valore dalla riga corrispondente di C2:C8. L' '1' in CONFRONTA indica una corrispondenza approssimativa, quindi assicurati che l'intervallo di ricerca sia ordinato correttamente.
- Se vuoi una corrispondenza esatta, usa
0come terzo argomento in CONFRONTA. - INDICE e CONFRONTA supportano anche orientamenti dei dati sia verticali che orizzontali.
- Se il valore non viene trovato, la formula restituisce #N/D; considera di avvolgerla con
SE.ERROREper output più amichevoli.
Restituisci un valore se un dato valore esiste in un certo intervallo utilizzando la funzione XLOOKUP
La funzione XLOOKUP è un'alternativa moderna per la ricerca di valori in Excel365 e Excel2019. XLOOKUP supera molte delle limitazioni di CERCA.VERT, come la restrizione sulla posizione della colonna di ricerca e l'abbinamento automatico esatto/approssimativo.
1. Nella cella di output desiderata (ad esempio, F2), digita la seguente formula:
=XLOOKUP(1, (E2>=A2:A8)*(E2<=B2:B8), C2:C8)Dopo aver inserito la formula, premi Invio per vedere il risultato nella cella selezionata.
- (E2>=A2:A8) controlla se E2 è maggiore o uguale a ciascun valore nella colonna A.
- (E2<=B2:B8) controlla se E2 è minore o uguale a ciascun valore nella colonna B.
- Moltiplicando queste due condizioni si crea un array di 1 e 0, dove 1 significa che E2 è compreso tra A e B in quella riga.
- XLOOKUP(1, ..., C2:C8) cerca il primo 1 e restituisce il valore corrispondente dalla colonna C.
- XLOOKUP si adatta dinamicamente se vengono inserite o spostate colonne, a differenza di CERCA.VERT con numeri di colonna fissi.
- Funziona sia per dati verticali che orizzontali.
- Richiede Excel 365 o 2021; per versioni precedenti, utilizza altri metodi descritti sopra.
Sblocca la Magia di Excel con Kutools AI
- Esecuzione Intelligente: Esegui operazioni sulle celle, analizza i dati e crea grafici — tutto guidato da semplici comandi.
- Formule Personalizzate: Genera formule su misura per ottimizzare i tuoi flussi di lavoro.
- Codifica VBA: Scrivi e implementa codice VBA senza sforzo.
- Interpretazione delle Formule: Comprendi facilmente formule complesse.
- Traduzione del Testo: Superare le barriere linguistiche all'interno dei tuoi fogli di calcolo.
Articoli correlati:
- Come utilizzare CERCA.VERT per restituire vero o falso / sì o no in Excel?
- Come utilizzare CERCA.VERT per restituire un valore nella cella adiacente o successiva in Excel?
- Come restituire un valore in un'altra cella se una cella contiene un certo testo in Excel?
I migliori strumenti per la produttività in Office
Potenzia le tue competenze in Excel con Kutools per Excel e sperimenta un'efficienza mai vista prima. Kutools per Excel offre oltre300 funzionalità avanzate per aumentare la produttività e farti risparmiare tempo. Clicca qui per ottenere la funzione di cui hai più bisogno...
Office Tab porta le schede su Office e rende il tuo lavoro molto più semplice
- Abilita la modifica e lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede della stessa finestra invece che in nuove finestre.
- Aumenta la produttività del50% e riduce centinaia di clic del mouse ogni giorno!
Tutti gli add-in Kutools. Un solo programma di installazione
La suite Kutools for Office include add-in per Excel, Word, Outlook & PowerPoint più Office Tab Pro, ideale per i team che lavorano su più app di Office.
- Suite tutto-in-uno — Add-in per Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un solo programma di installazione, una sola licenza — configurazione in pochi minuti (pronto per MSI)
- Funzionano meglio insieme — produttività ottimizzata su tutte le app Office
- Prova completa30 giorni — nessuna registrazione, nessuna carta di credito
- Massimo risparmio — costa meno rispetto all’acquisto singolo degli add-in