MATCH vs VLOOKUP

[kimberlie3]

Hoping someone can help me out!!! I want to search for a value from one column, into another column and output a different column....if that makes any sense.

I have a column of receivers numbers that detected. I have another column of all possible receivers, followed by 2 more columns of the lats/longs. I want to take my data set and look for it in my list of possibilities and output the lat/long.

Is this even a possibility without doing it manually (some of the datasets are quite large).

I attached an imaged of what it looks like and what I want it to output.

Any help would be greatly appreciated!!

 

[Legacy 143009]

Put this formula to X2:

=VLOOKUP($Q2, $S$2:$U$28, COLUMNS($A$1:A$1), 0)

Drag to right, drag to down.

 

[Peter_SSs]

Welcome to the MrExcel board!

We cannot see your column labels and your column descriptions seem a bit messed up to me, but with your versions you should be able to use the more efficient XLOOKUP.
See if you can adapt this tiny sample** to suit your layout. The formula in I2 is copied across and down.

** For the future, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

23 01 13.xlsm
	D	E	F	G	H	I	J
1	All	Lat	Long
2	104566	47.6	-55.1		119528	47.7	-55.2
3	119528	47.7	-55.2		104566	47.6	-55.1
4	119534	47.3	-55.3		119528	47.7	-55.2
5					119534	47.3	-55.3
Lookup

Cell Formulas
Range	Formula
I2:J5	I2	=XLOOKUP($H2,$D$2:$D$4,E$2:E$4)

 

[kimberlie3]

Welcome to the MrExcel board!

We cannot see your column labels and your column descriptions seem a bit messed up to me, but with your versions you should be able to use the more efficient XLOOKUP.
See if you can adapt this tiny sample** to suit your layout. The formula in I2 is copied across and down.

** For the future, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

23 01 13.xlsm
	D	E	F	G	H	I	J
1	All	Lat	Long
2	104566	47.6	-55.1		119528	47.7	-55.2
3	119528	47.7	-55.2		104566	47.6	-55.1
4	119534	47.3	-55.3		119528	47.7	-55.2
5					119534	47.3	-55.3
Lookup

Cell Formulas
Range	Formula
I2:J5	I2	=XLOOKUP($H2,$D$2:$D$4,E$2:E$4)

I tried your example but I just get "#NAME?" as the output. I tried to download XL2BB but I do not have admin rights on the computer I am using so it will not let me download.

 

[kimberlie3]

Put this formula to X2:

=VLOOKUP($Q2, $S$2:$U$28, COLUMNS($A$1:A$1), 0)

Drag to right, drag to down.

When I do that, it just gives me the receiver number and not the lat/long that corresponds to that receiver number unfortunately

 

[kimberlie3]

I tried your example but I just get "#NAME?" as the output. I tried to download XL2BB but I do not have admin rights on the computer I am using so it will not let me download.

I got it!!! Wohoo!! Thank you so much for your help

 

[Peter_SSs]

I tried your example but I just get "#NAME?"

In that case are you sure that your profile version information is correct? It shows that you have Excel 365 and 2021, both of which have XLOOKUP - the likely cause of the #NAME? error in older versions of Excel.

Edit: I was drafting my post when you last posted so I had missed that one. So, are you saying that it works with XLOOKUP now?