Searching for text strings in one cell, returning values from another

**AwagentX** · 01-19-2015, 01:30 PM

I have an inventory workbook with 3 tabs ("Count Sheet", "Report A", "Report B") we enter the inventory on the "Count Sheet", and I need to generate a formula to search that sheet for a value and return the value in an adjacent cell eg: search "count sheet" for "Widget 1" return "number on hand 2 cells to the right" so all the search terms will be in Column C and all all the counts will be in Column H any ideas on a simple / easy way to do this?

Thanks

TJ

**Glenn Kennedy** · 01-19-2015, 01:31 PM

Yep. Not difficult, but I can't second guess how your data re structured. Please, attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**Pete_UK** · 01-19-2015, 01:32 PM

You'd normally use VLOOKUP for this. Attach a sample workbook then it will be easier to see where "Widget 1" is selected.

Hope this helps.

Pete

**AwagentX** · 01-19-2015, 02:10 PM

Test Workbook.xls

we are filling the "on hand count" column (currently just an =cell formula)In the Report sheets from the "count sheet" but we need to search for the name i specify... (note, widget 1 may not be called widget 1 on all sheets, will need to specify value in formula)

the reason for this is we may add rows or columns in any place on that count sheet or reorder it as time goes on but it still needs to fill out the report sheets correctly...

Thanks

**Glenn Kennedy** · 01-19-2015, 02:35 PM

I'm not convinced I'm on the right lines here... especially when you say things like widget 1 might not always be widget 1... But take a look

**AwagentX** · 01-19-2015, 02:39 PM

the value in the column "item" may not be the same in all pages, it is in the example for clarity, but in the real sheet, it might be "widget 1" on the count sheet and on the "report a" sheet it might be "widget A" and on report b it could be "widget purple" so we can't reference the name on that sheet, the search string must be specified in the formula...

**Glenn Kennedy** · 01-19-2015, 02:44 PM

Do you mean that the one thing can have 3 different names, depending on... whatever? Or do you ean that there will always be a widget !, but it miht not be in the same place on all sheets?

**AwagentX** · 01-19-2015, 02:49 PM

Both, the order will not be the same on all 3 sheets by a long shot... and the two reports use different names (darn departmental differences), so i will need to manually enter the search string for each cell on the reports to search the count sheet... eg if the formula is looking for widget 1 i would edit a formula to be something like this:

='count sheet' find 'widget 1' return value from "column labeled 'total on hand'"

make sense?

i will have to enter the 'widget 1' value by hand in each cell to match what i'm looking for on the count sheet

**Glenn Kennedy** · 01-19-2015, 03:10 PM

OMG.... Ever thought of organising corporate floggings to promote organisational cohesion?

**AwagentX** · 01-19-2015, 03:17 PM

no, unfortunately our corporate culture is "no division can understand what the other does, under any circumstances" so that will never happen as much as I would love to... I figure there is some way to say hey excell look for text='widget 1' and return the value from the column in that row for 'number on hand'

before I started this project there were 5 workbooks for inventory, and we did 5 inventories each month...

**Glenn Kennedy** · 01-19-2015, 03:28 PM

There is, but it is SO manual. I'm away from my PC now. But if you put "WidgetXX" including the "s, after Match( replacing everything up to but excluding the first comma thereafter : it should work.

If not - shout. Il be back tomorrow am.

**AwagentX** · 01-19-2015, 03:36 PM

i don't see the formula glenn, is it an attachment i'm missing?

Thanks

**Glenn Kennedy** · 01-20-2015, 04:42 AM

Dohh. There was meant to be an attachment at Post 5. See report B, any of the pale green cells. This is the normal way of doing this. In your case, you may need to use the approach in the pink cell. To be honest, my earlier suggestion of corporate floggings would be preferable!!