# Vlookup or If/Then to return a substitute cell for a cell that matches itself in a range

1. ## Vlookup or If/Then to return a substitute cell for a cell that matches itself in a range

I know the title might seem confusing. This is what I'm trying to do.

I have a list of numbers in column A. Everything to the right of column A in the same row can be represented by the A cell. So A2:A40 are to be represented by A1, etc. I have another sheet where I have the list of all possible numbers. I want to run a formula that will return the value in the A1 cell for all numbers that have matches in A row. So in my other sheet, if A2 comes up, in the column next to it, I want A1 to appear. I'll attach a book to show what I mean. There are two tabs.

Thanks.

2. ## Re: Vlookup or If/Then to return a substitute cell for a cell that matches itself in a ran

Attached is a modified version of your posted workbook.
The items in sheet 'Table 1' are now all the results of formulas.

In cell B2:
``Please Login or Register  to view this content.``

In cell B3 and copied down to B16 (note that B16 is blank becuase the table's data has been exhausted):
``Please Login or Register  to view this content.``

In cell A2 and copied down:
``Please Login or Register  to view this content.``

3. ## Re: Vlookup or If/Then to return a substitute cell for a cell that matches itself in a ran

Now, can I apply this to a different book, with ranges of different sizes? I just used this small book as an example. My actual book is 249 rows deep and around 150 columns wide.

4. ## Re: Vlookup or If/Then to return a substitute cell for a cell that matches itself in a ran

Also, some of the rows in the other book don't have anything next to them in columns, all they have is a value in the A column, but I still want them to be accounted for in the other table.

5. ## Re: Vlookup or If/Then to return a substitute cell for a cell that matches itself in a ran

It should work with both of those conditions. Just update the appropriate range references

6. ## Re: Vlookup or If/Then to return a substitute cell for a cell that matches itself in a ran

The other issue is, in my other book, the first cell in Table 1 doesn't match the first cell in Table 2. Not all of them are in Table 2 either, I just want to the ones that do match to be returned to the second column in the first Table. Does that make sense? There are 10000 rows in Table 1, and just shy of 300 in Table 2.

7. ## Re: Vlookup or If/Then to return a substitute cell for a cell that matches itself in a ran

The range of Table 1, including column headings, is A1:B10598, and Table 2, including column headings, is A1:CL295

8. ## Re: Vlookup or If/Then to return a substitute cell for a cell that matches itself in a ran

Was your example file representative of your actual workbook? If so, you should be able to adapt the formulas. Otherwise, I'd have to see an example file that is actually representative of the workbook.

9. ## Re: Vlookup or If/Then to return a substitute cell for a cell that matches itself in a ran

This might be a better example. See how not everything has a match and not everything is in order? So for ones where there isnt a match from the second table, Nothing should show up in column B in the first table. Does that work?

10. ## Re: Vlookup or If/Then to return a substitute cell for a cell that matches itself in a ran

Way too late and not even a formula but hey! Here goes nuthin...

``Please Login or Register  to view this content.``

11. ## Re: Vlookup or If/Then to return a substitute cell for a cell that matches itself in a ran

Attached is a modified version of example book 2.

In sheet 'Table 1' cell B2 and copied down is this array formula:
``Please Login or Register  to view this content.``
Note that array formulas must be entered with Ctrl+Shift+Enter and not just Enter. That's how they get the curly braces {}. Do not try to add those yourself.

12. ## Re: Vlookup or If/Then to return a substitute cell for a cell that matches itself in a ran

Yep. That worked. Thanks tigeravatar. Another job well done.

13. ## Re: Vlookup or If/Then to return a substitute cell for a cell that matches itself in a ran

That's like a reversed VLOOKUP, how neat!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1