vlookup to look in a range for "What"

still learning · Sep 27, 2018

Hi

Can I use vlookup to look in a range instead of a cell
the range will only have 1 cell with data

=vlookup( A1:a9,data,0)
=vlookup(criteria,data,0)

Neither works

mike

Joe4 · Sep 27, 2018

VLOOKUP looks up a single value and tries to find it in a range of cells. So looking a "range" doesn't really make sense.
Can you explain your data and describe what it is you are actually trying to accomplish?
Is it that only one cell in your range will ever be populated at a time, and that is the value you want to look up?

still learning · Sep 27, 2018

Hi Joe4
Yes. There will always be only 1 cell that is populated in the range
The range is an advance filter criteria. Since that is 2 lines, I would name the line a different range
i was trying to find what line number the extract was in the data field and ended up adding a row with the line numbers.
I got to thinking that i might be able to use vlook to find it too. Always trying to learn
the criteria is generated from a userform box.

mike

I also going to post a question about macro looping next

GJSBED · Sep 27, 2018

You can identify a cell with the word What in it using countif =COUNTIF(J6,"*what*") you'd have to have it next to every cell in the range and it'll pop up '1' when there is a what in there.

That will at least get you part way maybe?

Joe4 · Sep 27, 2018

This shows you how you can get the last value populated in a defined range (if there is only one value, that would also be the last value): https://exceljet.net/formula/get-value-of-last-non-empty-cell
So, if you range was A1:A9, it would look like:

Code:

=LOOKUP(2,1/(A1:A9<>""),A1:A9)

To get the row number instead of the value, just a small edit:

Code:

=LOOKUP(2,1/(A1:A9<>""),ROW(A1:A9))

Is that what you are looking for?

still learning · Sep 27, 2018

nope
hope this explains better.
I had to add a column with the heading "line numbers" in "advance..filter..sort" and redo all the range info for it (data.criteria,extract).

It got me wondering if "vlookup" can be used to find the look-up value in a range (in this case, the criteria of the filter) instead of a single cell, as long as the range only has one value in it.

If yes, I might have been able to just add a heading in the extract (but not in the actual filter) with the vlookup formula based on the criteria...as long as there was only one value in that range

Mike

Joe4 · Sep 27, 2018

If I understand you correctly, you can use the second formula I proposed, which returns the single value from your range of cells, and use that as the first argument of your VLOOKUP function, i.e.
=VLOOKUP(LOOKUP(2,1/(A1:A9<>""),ROW(A1:A9)),lookup range, column to return, match type)

If that is not what you are looking for, then please try and post a simple example of what you data looks like and what you are trying to do, as I am not understanding it from your explanation.

still learning · Sep 27, 2018

Hi Joe4
I can't get the lookup formula to work.

I'm wondering if the value lookup from a vlookup function can be in a range (with only one cell with data in it that range).
I thought about this because i was trying to return a line number of where the value was so I could find it in a data range

thanks for your patients

mike

posting permissions says:
You may not post attachments

Joe4 · Sep 28, 2018

Actually, I had it backwards in my previous post. To get the value from the range to use in a VLOOKUP, you would use the first formula (which returns the value) and not the second (which returns the row number).

So that formula would look like this:
=VLOOKUP(LOOKUP(2,1/(A1:A9<>""),A1:A9),lookup range, column to return, match type)

posting permissions says:
You may not post attachments

You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html. Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

still learning · Sep 30, 2018

Thank you Joe4
you have been very helpful

mike

vlookup to look in a range for "What"

still learning

Well-known Member

Excel Facts

Joe4

MrExcel MVP, Junior Admin

still learning

Well-known Member

GJSBED

Board Regular

Joe4

MrExcel MVP, Junior Admin

still learning

Well-known Member

Joe4

MrExcel MVP, Junior Admin

still learning

Well-known Member

Joe4

MrExcel MVP, Junior Admin

still learning

Well-known Member

Similar threads

Forum statistics

Share this page

vlookup to look in a range for "What"

Well-known Member

Excel Facts

MrExcel MVP, Junior Admin

Well-known Member

Board Regular

MrExcel MVP, Junior Admin

Well-known Member

MrExcel MVP, Junior Admin

Well-known Member

MrExcel MVP, Junior Admin

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock